eDocs Home > BEA AquaLogic Data Services Platform Documentation > Data Services Developer's Guide > Contents

How To Handle Non-Unique Joins

This topic shows how to enable an update map when a logical data service uses a non-unique join between relational data sources.

Understand the Join

In a logical data service, you can join tables visually in the Query Map by dragging from a key element in one data source to a corresponding key element in another data source.

Joining Tables in the Query Map

You can also create a join by adding an XQuery WHERE statement in the expression editor or the Source tab:

where $CUSTOMER/CUSTOMER_ID eq $CREDIT_CARD/CUSTOMER_ID

If both tables are in the same database, the XML return type is nested, and you are joining on a unique key, ALDSP creates a left outer join. You can see the SQL in the query plan for the service (click the Plan tab, then Show Query Plan):

SELECT ...
FROM "RTLCUSTOMER"."CUSTOMER" t1
LEFT OUTER JOIN "RTLCUSTOMER"."ADDRESS" t2
ON (t1."CUSTOMER_ID" = t2."CUSTOMER_ID")

If the XML return type is flat, ALDSP creates an inner join, and the SQL looks like this:

SELECT ...
FROM "RTLAPPLOMS"."CUSTOMER_ORDER" t1
JOIN "RTLCUSTOMER"."CUSTOMER" t2
ON (t2."CUSTOMER_ID" = t1."C_ID")

A left outer join returns rows from the left (meaning, the first) table, even if they do not match any rows in the right (second) table.

An inner join requires that a value in the left table match a value in the right table in order for the left values to be included in the result. For example, you might match one customer to many orders, creating a joined table like this:

Here, CUSTOMER_ID is a unique key and has one row in the relational source. However, in the joined table, CUSTOMER1 has three orders and three rows. If you update information for CUSTOMER1 such as FIRST_NAME in the joined table, where each customer has multiple rows, the value to use to update the underlying data source is ambiguous.

With a non-unique join, all or part of the update map is temporarily disabled and looks like this:

A Disabled Update Block

When you click View Generate Log in the update map, you see a message like this one:

The primary read function has a non-unique join involving this data source.

In your function or procedure code, in the Source tab, you might see for statements directly nested within each other, without an intervening WHERE clause:

for $CUSTOMER in ns1:CUSTOMER()
for $CREDIT_CARD in ns2:CREDIT_CARD()
return

Or, you might see XML elements directly nested within each other without intervening SQL statements:

<ns7:CUSTOMER_PROFILE>
      <CUSTOMER>
        ...
      {
           <CREDIT_CARD>
              ...
           </CREDIT_CARD>
      }
      </CUSTOMER>
</ns7:CUSTOMER_PROFILE>

These are all symptoms of a non-unique join. You need to enable the update map so that you can deploy the service, test it, and make it available to client applications.

In an update map, the most common causes of a non-unique join are:

• A logical data service with a flat (non-nested) return type.
• An incorrect block scope in the query map.
• An incorrect table join, or no table join, in the query map.
• An attempt to join on a field other than a key field.

Correct the Block Scope

If your logical data service has a nested XML return type, scope the data sources to XML blocks within the return type.

1. In Query Map, click the zone icon   of a data source.
2. Drag the zone icon from the data source to the nested element in the return type.
3. Mouse over the zone icon in the data source. Verify that only the nested element is highlighted in the return type.

Checking the Scope in the Return Type

Correct the Table Join

You might also get a non-unique join if the data sources are not joined correctly. You can join the tables either visually in the Query Map or by entering a WHERE clause in the expression editor or the Source tab. Be sure to join tables on a key element, marked like this:

To join tables visually:

1. Click the Query Map tab.
2. Drag from a key element in one data source to the same key element in another data source (for example, $CUSTOMER/CUSTOMER_ID to $ADDRESS/CUSTOMER_ID).
3. Click the Source tab and expand the read function to check the location of the WHERE clause. For example, if your XML return type is nested, the XQuery code should also be nested:

for $CUSTOMER in ns1:CUSTOMER()
return
      ...
      for $CREDIT_CARD in ns2:CREDIT_CARD()
      where $CUSTOMER/CUSTOMER_ID eq $CREDIT_CARD/CUSTOMER_ID
      return
      ...

To use the expression editor:

1. Click the Query Map tab.
2. Click the For block of the data source you are joining to.
3. In the expression editor, click Add Where Clause .
4. After the Where keyword, add the elements to be joined (for example, $CUSTOMER/CUSTOMER_ID eq $CREDIT_CARD/CUSTOMER_ID).
5. Click Save .
6. Check the WHERE clause in the Source tab, as described above.

Remember that ALDSP creates a left outer join if both tables are in the same database and the XML return type is nested. If the XML return type is flat, ALDSP creates an inner join.

Enable Update Blocks and Procedures

If your service has a return type with a flat structure, you may get a non-unique join, even if the join is correct in the Query Map and the Source tab.

If this happens, or if all or part of the update map is disabled for any reason, you can enable an update block or the Create-Update-Delete procedures within the block.

To enable a disabled (yellow) update block:

1. Right-click in the block, and choose Enable.
   The update block should now have a white (enabled) background. The Create, Update, or Delete procedure icons might still appear red or yellow, if they are disabled. However, you should be able to test the primary read function.
2. Click the Test tab.
3. At Select Operation, choose the primary read function, and click Run.

To enable an update map procedure:

1. If an element is marked with a Warning icon indicating that a mapping is required, select it.
2. In the expression editor, give the element a value with the correct data type.
3. Continue for all disabled elements.
4. In the Test tab, test an update procedure to ensure that the value overrides you have entered do what you want.

Test a Non-Unique Join

Let's go back to the sample joined table data (which we can see in the Test tab, by choosing the primary read function and clicking Run):

In this case, the XML return type is flat, and ALDSP has created an inner join between the CUSTOMER and CUSTOMER_ORDER tables in underlying relational data sources. In the joined table view, one customer has many orders. The CUSTOMER_ID can appear multiple times, but the ORDER_ID is unique.

Once the update map is enabled, you can update data in either the CUSTOMER or CUSTOMER_ORDER table in the data sources:

1. Click a row in the joined table data, then click Edit.
2. Locate the correct node in the XML tree data, and expand it.
3. Click the value you want to change, then edit it.
4. Click Submit.

If you update TOTAL_ORDER_AMT, from the CUSTOMER_ORDER table, the amount changes in one row of the joined table view.

However, if you update EMAIL_ADDRESS, the email address changes in one row of the data source table and in all rows for that customer in the joined table view.

See Also

Concepts

• Recognize When Something is Wrong

How Tos

• Test a Read Function and Simple Update